package org.soenea.doitall.domain.model.person.data;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import org.dsrg.soenea.service.threadLocal.DbRegistry;

public class PersonFinder {
	
	public static final String SELECT_ALL = "SELECT p.userID, p.version, p.userName, p.firstName, p.lastName, p.password, p.groupNum FROM " + PersonTDG.TABLE + " AS p;";
	public static final String SELECT = "SELECT p.userID, p.version, p.userName, p.firstName, p.lastName, p.password, p.groupNum FROM " + PersonTDG.TABLE + " AS p WHERE p.userID=?;";
	public static final String SELECT_BY_NAME = "SELECT p.userID, p.version, p.userName, p.firstName, p.lastName, p.password, p.groupNum FROM " + PersonTDG.TABLE + " AS p WHERE p.userName=? AND p.password=?;";
	public static final String SELECT_NOT_IN_GROUP = "SELECT p.userID, p.version, p.userName, p.firstName, p.lastName, p.password, p.groupNum FROM " + PersonTDG.TABLE + " AS p WHERE p.groupNum<>?;";
	
	public static ResultSet findAll() throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(SELECT_ALL);
		return ps.executeQuery();
	}
	
	public static ResultSet find(long id) throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(SELECT);
		ps.setLong(1, id);
		return ps.executeQuery();
	}
	
	public static ResultSet findAllWithoutGroup(long id) throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(SELECT_NOT_IN_GROUP);
		ps.setLong(1, id);
		return ps.executeQuery();
	}
	
	public static ResultSet findByName(String userName, String password) throws SQLException {
		PreparedStatement ps = DbRegistry.getDbConnection().prepareStatement(SELECT_BY_NAME);
		ps.setString(1, userName);
		ps.setString(2, password);
		return ps.executeQuery(); 
	}

}
